/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package bookstore.util;

import bookstore.data.dto.BookDTO;
import bookstore.jaxb.Books;
import bookstore.jaxb.Books.Book;
import bookstore.jaxb.Order;
import bookstore.jaxb.OrderDetails;
import bookstore.jaxb.UserInfo;
import bookstore.validator.JAXBBookStoreValidationHandler;
import com.mysql.jdbc.Statement;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.io.OutputStream;
import java.io.StringReader;
import java.io.StringWriter;
import java.io.Writer;
import java.sql.PreparedStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.servlet.http.HttpServletResponse;
import javax.xml.XMLConstants;
import javax.xml.bind.JAXBContext;
import javax.xml.bind.JAXBException;
import javax.xml.bind.Marshaller;
import javax.xml.bind.PropertyException;
import javax.xml.bind.Unmarshaller;
import javax.xml.stream.XMLOutputFactory;
import javax.xml.stream.XMLStreamException;
import javax.xml.stream.XMLStreamWriter;
import javax.xml.transform.TransformerConfigurationException;
import javax.xml.transform.TransformerException;
import javax.xml.validation.Schema;
import javax.xml.validation.SchemaFactory;
import javax.xml.xpath.XPath;
import javax.xml.xpath.XPathConstants;
import javax.xml.xpath.XPathExpressionException;
import javax.xml.xpath.XPathFactory;
import org.apache.fop.apps.FOPException;
import org.w3c.dom.Document;
import org.w3c.dom.Node;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import java.sql.Date;
import javax.xml.transform.Result;
import javax.xml.transform.Source;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.sax.SAXResult;
import javax.xml.transform.stream.StreamSource;
import org.apache.fop.apps.Fop;
import org.apache.fop.apps.FopFactory;
import org.apache.xmlgraphics.util.MimeConstants;

/**
 *
 * @author Administrator
 */
public class Utilities {

    /**
     * Create XML for search AJAX.
     * @param filePath
     */
    public static void createXMLSourceFile(String filePath) {
        Connection con = null;
        PreparedStatement stm = null;
        ResultSet rs = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/xml_bookstore", "root", "");

            String sql = "Select * from books";
            stm = con.prepareStatement(sql);
            rs = stm.executeQuery();
            Books books = new Books();
            while (rs.next()) {

                String isbn = rs.getString("isbn");
                String title = rs.getString("title");
                String author = rs.getString("author");
                String categoryName = rs.getString("categoryname");
                float price = rs.getFloat("price");
                String desc = rs.getString("desc");
                String publisher = rs.getString("publisher");
                String url = rs.getString("url");

                Book book = new Book();
                book.setAuthor(author);
                book.setCategoryName(categoryName);
                book.setDesc(desc);
                book.setIsbn(isbn);
                book.setPrice(price);
                book.setPublisher(publisher);
                book.setTitle(title);
                book.setUrl(url);

                //Add new book to list 
                books.getBook().add(book);
            }

            //start Marshall
            JAXBContext ctx = JAXBContext.newInstance(books.getClass());
            Marshaller mar = ctx.createMarshaller();
            mar.setProperty(Marshaller.JAXB_ENCODING, "UTF-8");
            mar.setProperty(Marshaller.JAXB_FORMATTED_OUTPUT, Boolean.TRUE);
            mar.marshal(books, new File(filePath));

        } catch (JAXBException ex) {
            Logger.getLogger(Utilities.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(Utilities.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(Utilities.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public static UserInfo GetUserInfo(String userName) {
        Connection con = null;
        PreparedStatement stm = null;
        ResultSet rs = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/xml_bookstore", "root", "");

            String sql = "Select * from userinfo where username=?";
            stm = con.prepareStatement(sql);
            stm.setString(1, userName);
            rs = stm.executeQuery();
            UserInfo user = new UserInfo();
            while (rs.next()) {
                String uName = rs.getString("Username");
                String name = rs.getString("Name");
                String address = rs.getString("Address");
                String phone = rs.getString("Phone");

                user.setUsername(uName);
                user.setNane(name);
                user.setAddress(address);
                user.setPhone(phone);
            }
            return user;
        } catch (SQLException ex) {
            Logger.getLogger(Utilities.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(Utilities.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public static int GetNextOrderId(String colName, String table) {
        Connection con = null;
        PreparedStatement stm = null;
        ResultSet rs = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/xml_bookstore", "root", "");

            String sql = "Select * from " + table;
            stm = con.prepareStatement(sql);
            rs = stm.executeQuery();
            int result = 0;
            int max = 0;
            while (rs.next()) {
                result = Integer.parseInt(rs.getString(colName).substring(3, rs.getString(colName).length()));
                if (result > max) {
                    max = result;
                }

            }
            return max + 1;
        } catch (SQLException ex) {
            Logger.getLogger(Utilities.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(Utilities.class.getName()).log(Level.SEVERE, null, ex);
        }
        return -1;
    }

    /**
     * Send back response message to client
     * <Response>
     *      <Type>OK/NG</Type>
     *      <Message>msg</Message>
     * </Response>
     * @param msg
     * @param status
     */
    public static String createResponseMessage(String msg, String status) {
        try {
            XMLOutputFactory ouf = XMLOutputFactory.newInstance();
            StringWriter sw = new StringWriter();
            XMLStreamWriter writer = ouf.createXMLStreamWriter(sw);
            writer.writeStartDocument();
            writer.writeStartElement("Response");
            writer.writeStartElement("Type");
            writer.writeCharacters(status);
            writer.writeEndElement();
            writer.writeStartElement("Message");
            writer.writeCharacters(msg);
            writer.writeEndElement();
            writer.writeEndElement();

            return sw.toString();

        } catch (XMLStreamException ex) {
            Logger.getLogger(Utilities.class.getName()).log(Level.SEVERE, null, ex);
        }

        return null;

    }

    public static void createResponseMessage(Writer sw, String msg, String status) {
        try {
            XMLOutputFactory ouf = XMLOutputFactory.newInstance();
            XMLStreamWriter writer = ouf.createXMLStreamWriter(sw);
            writer.writeStartDocument();
            writer.writeStartElement("Response");
            writer.writeStartElement("Type");
            writer.writeCharacters(status);
            writer.writeEndElement();
            writer.writeStartElement("Message");
            writer.writeCharacters(msg);
            writer.writeEndElement();
            writer.writeEndElement();

        } catch (XMLStreamException ex) {
            Logger.getLogger(Utilities.class.getName()).log(Level.SEVERE, null, ex);
        }

    }

    public static Object customUnMarshall(String schemaPath, String xmlSource) {
        JAXBBookStoreValidationHandler validator;
        try {
            SchemaFactory schemaFactory = SchemaFactory.newInstance(XMLConstants.W3C_XML_SCHEMA_NS_URI);
            Schema schema = schemaFactory.newSchema(new File(schemaPath));
            InputSource inputSource = new InputSource(new StringReader(xmlSource));
            validator = new JAXBBookStoreValidationHandler();
            JAXBContext ctx = JAXBContext.newInstance("bookstore.jaxb");
            Unmarshaller u = ctx.createUnmarshaller();
            Object result = u.unmarshal(inputSource);
            return result;
        } catch (JAXBException ex) {
            Logger.getLogger(Utilities.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        } catch (SAXException ex) {
            Logger.getLogger(Utilities.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }

    public static Object customUnMarshall(String schemaPath, String xmlSource, HttpServletResponse response, boolean flush) throws IOException {
        JAXBBookStoreValidationHandler validator;
        try {
            SchemaFactory schemaFactory = SchemaFactory.newInstance(XMLConstants.W3C_XML_SCHEMA_NS_URI);
            Schema schema = schemaFactory.newSchema(new File(schemaPath));
            InputSource inputSource = new InputSource(new StringReader(xmlSource));
            validator = new JAXBBookStoreValidationHandler();
            JAXBContext ctx = JAXBContext.newInstance("bookstore.jaxb");
            Unmarshaller u = ctx.createUnmarshaller();
            u.setSchema(schema);
            Object result = u.unmarshal(inputSource);
            //Error occured while validate XML
            if (validator.isbIsError()) {
                createResponseMessage(response.getWriter(), validator.getMessage(), "NG");
                response.getWriter().flush();
                return null;
            } else {
                if (flush) {
                    createResponseMessage(response.getWriter(), validator.getMessage(), "OK");
                    response.getWriter().flush();
                }
                return result;
            }

        } catch (JAXBException ex) {
            Logger.getLogger(Utilities.class.getName()).log(Level.SEVERE, null, ex);
            createResponseMessage(response.getWriter(), ex.toString(), "NG");
            response.getWriter().flush();
            return null;
        } catch (SAXException ex) {
            Logger.getLogger(Utilities.class.getName()).log(Level.SEVERE, null, ex);
            createResponseMessage(response.getWriter(), ex.toString(), "NG");
            response.getWriter().flush();
            return null;
        }
    }

    public static Object customUnMarshall(String schemaPath, File file, HttpServletResponse response, boolean flush) throws IOException {
        JAXBBookStoreValidationHandler validator;
        try {
            SchemaFactory schemaFactory = SchemaFactory.newInstance(XMLConstants.W3C_XML_SCHEMA_NS_URI);
            File schemaFile = new File("D:\\BooksSchema.xml");
            //Schema schema = schemaFactory.newSchema(schemaFile);
            validator = new JAXBBookStoreValidationHandler();
            JAXBContext ctx = JAXBContext.newInstance(Books.class);
            Unmarshaller u = ctx.createUnmarshaller();
            //u.setSchema(schema);
            Source orderDetailSource = new StreamSource(file);
            Object result = u.unmarshal(orderDetailSource, Books.class);
            //Error occured while validate XML
            if (validator.isbIsError()) {
                createResponseMessage(response.getWriter(), validator.getMessage(), "NG");
                response.getWriter().flush();
                return null;
            } else {
                if (flush) {
                    createResponseMessage(response.getWriter(), validator.getMessage(), "OK");
                    response.getWriter().flush();
                }
                return result;
            }


        } catch (JAXBException ex) {
            Logger.getLogger(Utilities.class.getName()).log(Level.SEVERE, null, ex);
            createResponseMessage(response.getWriter(), ex.toString(), "NG");
            response.getWriter().flush();
            return null;
//        } catch (SAXException ex) {
//            Logger.getLogger(Utilities.class.getName()).log(Level.SEVERE, null, ex);
//            createResponseMessage(response.getWriter(), ex.toString(), "NG");
//            response.getWriter().flush();
//            return null;
        }
    }

    public static String insertOrder(Order order, OrderDetails orderDetails, Document doc, String filePath) {

        Connection con = null;
        PreparedStatement stm = null;
        String orderFilePath = "";
        String orderDetailFilePath = "";
        String orderID = "";
        int rs = 0;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/xml_bookstore", "root", "");
            //get next order ID;
            orderID = "ORI" + GetNextOrderId("OrderId", "orders");
            double totalPrice = 0;
            //order.setOrderId(orderID);
            //INSERT TO TABLE ORDERS
            String sql = "INSERT orders VALUES(?,?,?,?,?,?,?)";
            stm = con.prepareStatement(sql);

            DateFormat df = new SimpleDateFormat("dd-MM-yyyy");
            java.util.Date orderDate = df.parse(order.getOrderTime());
            java.sql.Date transformerDate = new Date(orderDate.getTime());
            stm.setString(1, orderID);
            stm.setDate(2, transformerDate);
            stm.setString(3, order.getAddress());
            stm.setString(4, order.getCity().value());
            if (order.getUsername().equals("undefined")){
                stm.setString(6, "GUEST");
            }else{
                stm.setString(5, order.getUsername());
            }
            
            stm.setString(6, order.getName());
            stm.setString(7, order.getPhone());

            rs = stm.executeUpdate();

            XMLOutputFactory ouf = XMLOutputFactory.newInstance();
            StringWriter sw = new StringWriter();
            orderFilePath = filePath + "OrderToPDF" + orderID + ".xml";
            File orderFile = new File(orderFilePath);
            XMLStreamWriter writer = ouf.createXMLStreamWriter(new FileWriter(orderFile));
            writer.writeStartDocument();
            writer.writeStartElement("Order");
            writer.writeStartElement("OrderId");
            writer.writeCharacters(orderID);
            writer.writeEndElement();
            writer.writeStartElement("OrderTime");
            writer.writeCharacters(order.getOrderTime());
            writer.writeEndElement();
            writer.writeStartElement("Address");
            writer.writeCharacters(order.getAddress());
            writer.writeEndElement();
            writer.writeStartElement("City");
            writer.writeCharacters(order.getCity().value());
            writer.writeEndElement();
            //UPDATE CODE for USER NOT LOGIN insert GUEST
            if (order.getUsername().equals("undefined")) {
                writer.writeStartElement("Username");
                writer.writeCharacters("GUEST");
            } else {
                writer.writeStartElement("Username");
                writer.writeCharacters(order.getUsername());
            }

            writer.writeEndElement();
            writer.writeStartElement("Name");
            writer.writeCharacters(order.getName());
            writer.writeEndElement();
            writer.writeStartElement("Phone");
            writer.writeCharacters(order.getPhone());
            writer.writeEndElement();
            writer.writeEndElement();
            writer.writeEndDocument();
            writer.flush();

            //Insert OrdersDetails ID
            orderDetailFilePath = filePath + "OrderDetailsToPDF" + orderID + ".xml";
            File orderDetailFile = new File(orderDetailFilePath);
            writer = ouf.createXMLStreamWriter(new FileWriter(orderDetailFile));
            writer.writeStartDocument();
            writer.writeStartElement("OrderDetails");
            int orderDetailsStartID = GetNextOrderId("OrderDetailId", "orderdetails");
            for (int i = 0; i < orderDetails.getOrderDetail().size(); i++) {
                writer.writeStartElement("OrderDetail");
                String orderDetailsId = "ODI" + (orderDetailsStartID + i);
                sql = "INSERT orderdetails VALUES(?,?,?,?)";
                stm = con.prepareStatement(sql);
                stm.setString(1, orderDetailsId);
                stm.setInt(2, orderDetails.getOrderDetail().get(i).getQuantity());
                totalPrice = totalPrice + (double) orderDetails.getOrderDetail().get(i).getQuantity() * GetPrice(doc, orderDetails.getOrderDetail().get(i).getIsbn());
                stm.setString(3, orderDetails.getOrderDetail().get(i).getIsbn());
                stm.setString(4, orderID);
                rs = stm.executeUpdate();

                //Output to file
                writer.writeStartElement("OrderDetailId");
                writer.writeCharacters(orderDetailsId);
                writer.writeEndElement();
                writer.writeStartElement("Isbn");
                writer.writeCharacters(orderDetails.getOrderDetail().get(i).getIsbn());
                writer.writeEndElement();
                writer.writeStartElement("Title");
                writer.writeCharacters(GetTitle(doc, orderDetails.getOrderDetail().get(i).getIsbn()));
                writer.writeEndElement();
                writer.writeStartElement("Quantity");
                int quantity = orderDetails.getOrderDetail().get(i).getQuantity();
                writer.writeCharacters(Integer.toString(quantity));
                writer.writeEndElement();
                writer.writeStartElement("Price");
                double price = GetPrice(doc, orderDetails.getOrderDetail().get(i).getIsbn());
                writer.writeCharacters(Double.toString(price));
                writer.writeEndElement();
                writer.writeStartElement("SubTotal");
                double subTotal = price * quantity;
                writer.writeCharacters(Double.toString(subTotal));
                writer.writeEndElement();
                writer.writeEndElement();
            }
            writer.writeEndElement();
            writer.writeEndDocument();
            writer.flush();
            writer.close();
            if (rs == 0) {
                return null;
            } else {
                return orderID;
            }

        } catch (XMLStreamException ex) {
            Logger.getLogger(Utilities.class.getName()).log(Level.SEVERE, null, ex);
        } catch (IOException ex) {
            Logger.getLogger(Utilities.class.getName()).log(Level.SEVERE, null, ex);
        } catch (XPathExpressionException ex) {
            Logger.getLogger(Utilities.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ParseException ex) {
            Logger.getLogger(Utilities.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(Utilities.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(Utilities.class.getName()).log(Level.SEVERE, null, ex);
        }

        return null;

    }

    public static double GetPrice(Document doc, String isbn) throws XPathExpressionException {
        XPathFactory xpf = XPathFactory.newInstance();
        XPath xpath = xpf.newXPath();
        String expression = "//Book[Isbn =\"" + isbn + "\"]/Price";
        Node result = (Node) xpath.evaluate(expression, doc, XPathConstants.NODE);
        return Double.parseDouble(result.getTextContent());
    }

    public static String GetTitle(Document doc, String isbn) throws XPathExpressionException {
        XPathFactory xpf = XPathFactory.newInstance();
        XPath xpath = xpf.newXPath();
        String expression = "//Book[Isbn =\"" + isbn + "\"]/Title";
        Node result = (Node) xpath.evaluate(expression, doc, XPathConstants.NODE);
        return result.getTextContent();
    }

    public static void GeneratePDF(String xsltSource, String outputFile, String orderSourceFile, String orderDetailSourceFile) {
        {
            OutputStream out = null;
            try {
                FopFactory fopFact = FopFactory.newInstance();
                out = new BufferedOutputStream(new FileOutputStream(outputFile));
                Fop fop = fopFact.newFop(MimeConstants.MIME_PDF, out);
                Source orderDetailSource = new StreamSource(new File(orderDetailSourceFile));

                Source xslt = new StreamSource(new File(xsltSource));
                TransformerFactory factory = TransformerFactory.newInstance();
                Transformer transformer = factory.newTransformer(xslt);
                transformer.setParameter("OrderFileName", orderSourceFile);
                Result res = new SAXResult(fop.getDefaultHandler());

                transformer.transform(orderDetailSource, res);

                out.close();

            } catch (IOException ex) {
                Logger.getLogger(Utilities.class.getName()).log(Level.SEVERE, null, ex);
            } catch (TransformerException ex) {
                Logger.getLogger(Utilities.class.getName()).log(Level.SEVERE, null, ex);
            } catch (FOPException ex) {
                Logger.getLogger(Utilities.class.getName()).log(Level.SEVERE, null, ex);
            } finally {
                try {
                    out.close();
                } catch (IOException ex) {
                    Logger.getLogger(Utilities.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }


    }

    public static String GetHomePageHtml() {
        Connection con = null;
        PreparedStatement stm = null;
        ResultSet rs = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/xml_bookstore", "root", "");

            String sql = "Select * from books LIMIT 10";
            stm = con.prepareStatement(sql);
            rs = stm.executeQuery();
            Books books = new Books();
            while (rs.next()) {

                String isbn = rs.getString("isbn");
                String title = rs.getString("title");
                String author = rs.getString("author");
                String categoryName = rs.getString("categoryname");
                float price = rs.getFloat("price");
                String desc = rs.getString("desc");
                String publisher = rs.getString("publisher");
                String url = rs.getString("url");

                Book book = new Book();
                book.setAuthor(author);
                book.setCategoryName(categoryName);
                book.setDesc(desc);
                book.setIsbn(isbn);
                book.setPrice(price);
                book.setPublisher(publisher);
                book.setTitle(title);
                book.setUrl(url);

                //Add new book to list
                books.getBook().add(book);
            }
            String result = "";
            for (int i = 0; i < books.getBook().size(); i++) {
                result += "<div>";
                result += "<img class=\"coverUrl\" src=\"";
                result += books.getBook().get(i).getUrl();
                result += "\" alt = \"\"/>";
                result += "<div class=\"desc\">";
                result += "<div class=\"title\"><a href=\"Controller?action=BookDetails&isbn=" + books.getBook().get(i).getIsbn() + "\">" + books.getBook().get(i).getTitle() + "</a></div>";
                result += "<div class=\"author\">" + books.getBook().get(i).getAuthor() + "</div>";
                result += "<button class=\"btnAddCart\" onclick=\"addToCart(this)\" data-isbn=\"" + books.getBook().get(i).getIsbn() + "\" data-title=\"" + books.getBook().get(i).getTitle() + "\" data-author=\"" + books.getBook().get(i).getAuthor() + "\" data-cover=\"" + books.getBook().get(i).getUrl() + "\" data-price=\"" + books.getBook().get(i).getPrice() + "\"><span class=\"price\">$" + books.getBook().get(i).getPrice() + "</span>Add to Cart</button>";
                result += "</div>";
                result += "</div>";
            }
            return result;
        } catch (SQLException ex) {
            Logger.getLogger(Utilities.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(Utilities.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }
}
